﻿using System;
using System.IO;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using DevExpress.XtraEditors;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.Parameters;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using prjKQXS_UI.Hop_dong;
using prjKQXS_UI.Reports;
using prjKQXS_UI.View;
using prjKQXS_DLL;

namespace prjKQXS_UI
{
    public partial class frmBaoCao : Form
    {
        private SqlConnection conn = new SqlConnection(BienTc.connString);
        frmThanhLy frmThanhly = null;
        dsReports ds = new dsReports();

        int nam, thang, denThang;
        char MaDV;
            string  TenDV;

        public frmBaoCao(frmThanhLy frmThanhly)
        {
            InitializeComponent();
            this.frmThanhly = frmThanhly;
        }

        private string formBaoCao = "";
        public frmBaoCao(int nam, int thang, int denThang, char madv, string tendv, string formBaoCao)
        {
            InitializeComponent();

            this.nam = nam;
            this.thang = thang;
            this.denThang = denThang;
            this.MaDV = madv;
            this.TenDV = tendv;

            this.formBaoCao = formBaoCao;
        }

        frmDMHopDong frmHOPDONG = null;
        public frmBaoCao(frmDMHopDong frmHOPDONG)
        {
            InitializeComponent();
            this.frmHOPDONG = frmHOPDONG;
        }

        string baocao = "";
        public frmBaoCao(string baocao)
        {
            InitializeComponent();
            this.baocao = baocao;
        }

        public frmBaoCao()
        {
            InitializeComponent();
        }

        string fileName = "";
        private void frmBaoCao_Load(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
            ReportDocument cryRpt = new ReportDocument();

            clsThanhLy cls = new clsThanhLy();
            cls.OnAction += new clsThanhLy.OnActionHandler(cls_OnAction);
            cls.OnError += new clsThanhLy.OnErrorHandler(cls_OnError);

            if (frmThanhly != null)
            {
                DataTable dtHopDong = null;
                if (frmThanhly.idHD > 0) dtHopDong = cls.loadData(frmThanhly.idHD);

                if (!File.Exists(@"BaoCao\Thanhly.rpt"))
                {
                    clsCommon.MsgBox("Không tìm thấy đường dẫn file " + Application.StartupPath + @"\BaoCao\Thanhly.rpt vui lòng kiểm tra lại !", 0);
                }
                else
                {
                    cryRpt.Load(@"BaoCao\Thanhly.rpt");
                    cryRpt.DataDefinition.FormulaFields["So_HD"].Text = "\"" + dtHopDong.Rows[0]["Ma_DT"] + "\"";
                    string ngayhd = "";
                    if (dtHopDong.Rows[0]["Ngay_HD"] != null && dtHopDong.Rows[0]["Ngay_HD"].ToString() != "")
                    {
                        ngayhd = DateTime.Parse(dtHopDong.Rows[0]["Ngay_HD"].ToString()).ToString("dd/MM/yyyy");
                    }
                    else
                    {
                        ngayhd = "";
                    }
                    cryRpt.DataDefinition.FormulaFields["Ngay_HD"].Text = "\"" + ngayhd + "\"";
                    cryRpt.DataDefinition.FormulaFields["Ho_ten"].Text = "\"" + dtHopDong.Rows[0]["Ten_DaiLy"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["Dia_chi"].Text = "\"" + dtHopDong.Rows[0]["Noi_O"] + "\"";
                    string ngaytl = "";
                    if (dtHopDong.Rows[0]["Ngay_ThanhLy"] != null && dtHopDong.Rows[0]["Ngay_ThanhLy"].ToString() != "")
                    {
                        ngaytl = DateTime.Parse(dtHopDong.Rows[0]["Ngay_ThanhLy"].ToString()).ToString("dd/MM/yyyy");
                    }
                    else
                    {
                        ngaytl = "";
                    }
                    if (dtHopDong.Rows[0]["Ma_DV"].ToString() == "8")
                    {
                        cryRpt.DataDefinition.FormulaFields["ThanhPho"].Text = "\" đồng thời làm thủ tục rút tiền ký quỹ (nếu có) \"";
                        cryRpt.DataDefinition.FormulaFields["Ten_DV"].Text = "\"" + dtHopDong.Rows[0]["Ten_DV"] + "\"";
                    }
                    else
                    {
                        cryRpt.DataDefinition.FormulaFields["ThanhPho"].Text = "";
                        cryRpt.DataDefinition.FormulaFields["Ten_DV"].Text = "\"" + dtHopDong.Rows[0]["Ten_DV"] + "\"";
                    }
                    cryRpt.DataDefinition.FormulaFields["Ngay_TLY"].Text = "\"" + ngaytl + "\"";
                    cryRpt.DataDefinition.FormulaFields["Ly_do"].Text = "\"" + dtHopDong.Rows[0]["Ly_Do"] + "\"";
                    crystalReportViewer2.ReportSource = cryRpt;
                    crystalReportViewer2.Refresh();
                }
            }
            else if (frmHOPDONG != null)
            {
                DataTable dt = new DataTable();
                if (frmHOPDONG.id > 0)dt = cls.loadData(frmHOPDONG.id);
                if (!File.Exists(frmHOPDONG.path.ToString()))
                {
                    clsCommon.MsgBox("Không tìm thấy đường dẫn file " + frmHOPDONG.path.ToString()+" vui lòng kiểm tra lại !", 0);
                }
                else
                {
                    cryRpt.Load(frmHOPDONG.path.ToString());
                    cryRpt.DataDefinition.FormulaFields["So_HD"].Text = "\"" + dt.Rows[0]["Ma_DT"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["Ten_DaiLy"].Text = "\"" + dt.Rows[0]["Ten_DaiLy"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["Noi_O"].Text = "\"" + dt.Rows[0]["Noi_O"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["CMT"].Text = "\"" + dt.Rows[0]["CMT"] + "\"";
                    
                    string ngaycap = "";
                    if (dt.Rows[0]["Ngay_Cap"] != null && dt.Rows[0]["Ngay_Cap"].ToString() != "")
                        ngaycap = DateTime.Parse(dt.Rows[0]["Ngay_Cap"].ToString()).ToString("dd/MM/yyyy");
                    else ngaycap = "";

                    cryRpt.DataDefinition.FormulaFields["Ngay_Cap"].Text = "\"" + ngaycap + "\"";
                    cryRpt.DataDefinition.FormulaFields["noi_cap"].Text = "\"" + dt.Rows[0]["Noi_Cap"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["MST"].Text = "\"" + dt.Rows[0]["MST"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["Ma_DLY"].Text = "\"" + dt.Rows[0]["Ma_DaiLy"] + "\"";
                    cryRpt.DataDefinition.FormulaFields["Noi_Ban"].Text = "\"" + dt.Rows[0]["Noi_Ban"] + "\"";                    
                    string a= DateTime.Parse(dt.Rows[0]["Ngay_HD"].ToString()).ToString("dd/MM/yyyy");
                    cryRpt.DataDefinition.FormulaFields["Ngay_HD"].Text = "\"" + a + "\"";
                    crystalReportViewer2.ReportSource = cryRpt;
                    crystalReportViewer2.Refresh();
                }
            }
            else if (formBaoCao == "frmBienDongDlyCap2")
            {

                fileName= Application.StartupPath + @"\BaoCao\DSDaily.rpt";
                if (!File.Exists(fileName))
                {
                    clsCommon.MsgBox("Không tìm thấy đường dẫn file " + fileName + " vui lòng kiểm tra lại !", 0);
                }
                else
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    cryRpt.Load(fileName);

                    DataTable dt = null;
                    SqlDataAdapter da = null;
                    int Nam = nam;
                    int Thang = thang;
                    int denthang = denThang;
                    if (Thang == denthang)
                    {
                        if (MaDV == '%')
                        {
                            string sql = "Select * from HopDong INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCANBO.Ma_DT " +
                                "INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCANBO.Ma_DMDonVi " +
                                "Where HOPDONG.isPublic=1 and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Datepart(Month,Ngay_HD)=" + 
                                Thang + " and Datepart(year,Ngay_HD)=" + Nam + "";
                            dt = clsCommon.FillData(sql);
                            foreach (DataRow rowDT in dt.Rows)
                            {
                                if (rowDT["Thanh_Ly"].ToString().Equals("1") || rowDT["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                {
                                    rowDT["Thanh_Ly"] = "FALSE";
                                }
                            }
                            da = new SqlDataAdapter("Select * from HOpdong INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCANBO.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCANBO.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Thanh_Ly =1 and Datepart(Month,Ngay_ThanhLy)=" + Thang + " and Datepart(year,Ngay_ThanhLy)=" + Nam + "", conn);
                            DataTable dtTL = new DataTable();
                            da.Fill(dtTL);
                            dt.Merge(dtTL);

                            foreach (DataRow rowNew in dt.Rows)
                            {
                                if (rowNew["Thanh_Ly"].ToString().Equals("1") || rowNew["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                {
                                    rowNew["Ly_Do"] = rowNew["Ma_DT"];
                                    rowNew["Ma_DT"] = "";
                                    rowNew["Ngay_HD"] = DBNull.Value;
                                }
                                else
                                {
                                    rowNew["Ly_Do"] = "";
                                    rowNew["Ngay_ThanhLy"] = DBNull.Value;
                                }
                            }

                            cryRpt.Database.Tables[0].SetDataSource(dt);
                            string tongDLDauThang = "";
                            DateTime dauthang = clsCommon.maxNgay(Nam, Thang - 1);
                            SqlCommand cmd = null;

                            cmd = new SqlCommand(@"Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNer JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Datepart(day,Ngay_HD) <= Datepart(day,'" + dauthang.ToString("MM/dd/yyyy") + "') and Datepart(Month,Ngay_HD) <= Datepart(Month,'" + dauthang.ToString("MM/dd/yyyy") + "') and "
                        + " Datepart(year,Ngay_HD) <= Datepart(year,'" + dauthang.ToString("MM/dd/yyyy") + "') and (Ngay_ThanhLy is null or Datepart(Month, Ngay_ThanhLy) > " + (Thang - 1).ToString() + ")", conn);
                            tongDLDauThang = cmd.ExecuteScalar().ToString();
                            cryRpt.DataDefinition.FormulaFields["dau_ky"].Text = "\"" + tongDLDauThang + " đại lý" + "\"";

                            string biendongdltrongthang = "";
                            string sotangtrongthang = "";
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNer JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1  and datepart(Month,Ngay_HD) = " + Thang + " and datepart(year,Ngay_HD) = " + Nam + "", conn);
                            sotangtrongthang = cmd.ExecuteScalar().ToString();
                            int sodlgiamtrongthang = 0;
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and HOPDONG.Thanh_Ly = 1 and  Datepart(Month,Ngay_ThanhLy) = " + Thang + " and Datepart(Year,Ngay_ThanhLy) = " + Nam + "", conn);
                            sodlgiamtrongthang = (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);
                            biendongdltrongthang = ((int)clsCommon.Parse(sotangtrongthang, 0) + sodlgiamtrongthang).ToString();
                            cryRpt.DataDefinition.FormulaFields["tanggiam"].Text = "\"" + biendongdltrongthang + " đại lý" + "\"";
                            int sodldencuoithang = 0;
                            sodldencuoithang = (int)clsCommon.Parse(tongDLDauThang, 0) + (int)clsCommon.Parse(sotangtrongthang, 0) - sodlgiamtrongthang;
                            cryRpt.DataDefinition.FormulaFields["cuoi_ky"].Text = "\"" + sodldencuoithang.ToString() + " đại lý" + "\"";

                            cryRpt.DataDefinition.FormulaFields["thang1"].Text = "\"" + "Tháng " + Thang.ToString() + " Năm " + Nam.ToString() + "\"";
                            
                            cryRpt.DataDefinition.FormulaFields["Don_Vi"].Text = "\"" + TenDV.ToUpper()  + "\"";

                        }
                        else
                        {
                            da = new SqlDataAdapter("Select * from HopDong INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCANBO.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCANBO.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Datepart(Month,Ngay_HD)=" + Thang + " and Datepart(year,Ngay_HD)=" + Nam + " and DMDONVI.Ma_DT=@madonvi", conn);
                            da.SelectCommand.Parameters.AddWithValue("@madonvi", MaDV);
                            dt = new DataTable();
                            da.Fill(dt);
                            foreach (DataRow rowDT in dt.Rows)
                            {
                                if (rowDT["Thanh_Ly"].ToString().Equals("1") || rowDT["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                    rowDT["Thanh_Ly"] = "FALSE";
                            }
                            da = new SqlDataAdapter("Select * from HOpdong INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCANBO.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCANBO.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Thanh_Ly =1 and Datepart(Month,Ngay_ThanhLy)=" + Thang + " and Datepart(year,Ngay_ThanhLy)=" + Nam + " and DMDONVI.Ma_DT=@madonvi", conn);
                            da.SelectCommand.Parameters.AddWithValue("@madonvi", MaDV);
                            DataTable dtTL = new DataTable();
                            da.Fill(dtTL);
                            dt.Merge(dtTL);

                            foreach (DataRow rowNew in dt.Rows)
                            {
                                if (rowNew["Thanh_Ly"].ToString().Equals("1") || rowNew["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                {
                                    rowNew["Ly_Do"] = rowNew["Ma_DT"];
                                    rowNew["Ma_DT"] = "";
                                    rowNew["Ngay_HD"] = DBNull.Value;
                                }
                                else
                                {
                                    rowNew["Ly_Do"] = "";
                                    rowNew["Ngay_ThanhLy"] = DBNull.Value;
                                }
                            }

                            cryRpt.Database.Tables[0].SetDataSource(dt);
                            string tongDLDauThang = "";
                            DateTime dauthang = clsCommon.maxNgay(Nam, Thang - 1);
                            SqlCommand cmd = null;

                            cmd = new SqlCommand(@"Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNer JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Datepart(day,Ngay_HD) <= Datepart(day,'" + dauthang.ToString("MM/dd/yyyy") + "') and Datepart(Month,Ngay_HD) <= Datepart(Month,'" + dauthang.ToString("MM/dd/yyyy") + "') and "
                        + " Datepart(year,Ngay_HD) <= Datepart(year,'" + dauthang.ToString("MM/dd/yyyy") + "') and (Ngay_ThanhLy is null or Datepart(Month, Ngay_ThanhLy) > " + (Thang - 1).ToString() + ") and DMDONVI.Ma_DT=@madonvi", conn);
                            cmd.Parameters.AddWithValue("@madonvi", MaDV);
                            tongDLDauThang = cmd.ExecuteScalar().ToString();
                            cryRpt.DataDefinition.FormulaFields["dau_ky"].Text = "\"" + tongDLDauThang + " đại lý" + "\"";

                            string biendongdltrongthang = "";
                            string sotangtrongthang = "";
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCANBO ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNer JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi Where HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1  and datepart(Month,Ngay_HD) = " + Thang + " and datepart(year,Ngay_HD) = " + Nam + "and DMDONVI.Ma_DT=@madonvi", conn);
                            cmd.Parameters.AddWithValue("@madonvi", MaDV);
                            sotangtrongthang = cmd.ExecuteScalar().ToString();
                            int sodlgiamtrongthang = 0;
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and HOPDONG.Thanh_Ly = 1 and  Datepart(Month,Ngay_ThanhLy) = " + Thang + " and Datepart(Year,Ngay_ThanhLy) = " + Nam + "and DMDONVI.Ma_DT=@madonvi", conn);
                            cmd.Parameters.AddWithValue("@madonvi", MaDV);
                            sodlgiamtrongthang = (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);
                            biendongdltrongthang = ((int)clsCommon.Parse(sotangtrongthang, 0) + sodlgiamtrongthang).ToString();
                            cryRpt.DataDefinition.FormulaFields["tanggiam"].Text = "\"" + biendongdltrongthang + " đại lý" + "\"";
                            int sodldencuoithang = 0;
                            sodldencuoithang = (int)clsCommon.Parse(tongDLDauThang, 0) + (int)clsCommon.Parse(sotangtrongthang, 0) - sodlgiamtrongthang;
                            cryRpt.DataDefinition.FormulaFields["cuoi_ky"].Text = "\"" + sodldencuoithang.ToString() + " đại lý" + "\"";

                            cryRpt.DataDefinition.FormulaFields["thang1"].Text = "\"" + "Tháng " + Thang.ToString() + " Năm " + Nam.ToString() + "\"";
                            cryRpt.DataDefinition.FormulaFields["Don_Vi"].Text = "\"" + TenDV.ToUpper() + "\"";
                        }
                    }
                    else
                    {
                        if (MaDV == '%')
                        {
                            da = new SqlDataAdapter("Select * from HopDong INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Datepart(Month,Ngay_HD) >=" + Thang + " and Datepart(Month,Ngay_HD) <=" + denthang + " and Datepart(year,Ngay_HD)=" + Nam + "", conn);
                            dt = new DataTable();
                            da.Fill(dt);
                            foreach (DataRow rowDT in dt.Rows)
                            {
                                if (rowDT["Thanh_Ly"].ToString().Equals("1") || rowDT["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                    rowDT["Thanh_Ly"] = "FALSE";
                            }
                            da = new SqlDataAdapter("Select * from HOpdong INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Thanh_Ly =1 and Datepart(Month,Ngay_ThanhLy) >=" + Thang + " and Datepart(Month,Ngay_ThanhLy) <=" + denthang + " and Datepart(year,Ngay_ThanhLy)=" + Nam + "", conn);
                            DataTable dtTL = new DataTable();
                            da.Fill(dtTL);
                            dt.Merge(dtTL);

                            foreach (DataRow rowNew in dt.Rows)
                            {
                                if (rowNew["Thanh_Ly"].ToString().Equals("1") || rowNew["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                {
                                    rowNew["Ly_Do"] = rowNew["Ma_DT"];
                                    rowNew["Ma_DT"] = "";
                                    rowNew["Ngay_HD"] = DBNull.Value;
                                }
                                else
                                {
                                    rowNew["Ly_Do"] = "";
                                    rowNew["Ngay_ThanhLy"] = DBNull.Value;
                                }
                            }
                            cryRpt.Database.Tables[0].SetDataSource(dt);
                            string tongDLDauThang = "";
                            DateTime dauthang = clsCommon.maxNgay(Nam, Thang - 1);
                            SqlCommand cmd = null;
                            cmd = new SqlCommand(@"Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1  and Datepart(day,Ngay_HD) <= Datepart(day,'" + dauthang.ToString("MM/dd/yyyy") + "') and Datepart(Month,Ngay_HD) <= Datepart(Month,'" + dauthang.ToString("MM/dd/yyyy") + "') and "
                        + " Datepart(year,Ngay_HD) <= Datepart(year,'" + dauthang.ToString("MM/dd/yyyy") + "')  and (Ngay_ThanhLy is null or Datepart(Month, Ngay_ThanhLy) > " + (Thang - 1).ToString() + ")", conn);
                            tongDLDauThang = cmd.ExecuteScalar().ToString();
                            cryRpt.DataDefinition.FormulaFields["dau_ky"].Text = "\"" + tongDLDauThang + " đại lý" + "\"";

                            string biendongdltrongthang = "";
                            string sotangtrongthang = "";
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1  and datepart(Month,Ngay_HD) >= " + Thang + " and datepart(Month,Ngay_HD) <= " + denthang + "  and datepart(year,Ngay_HD) = " + Nam + "", conn);
                            sotangtrongthang = cmd.ExecuteScalar().ToString();
                            int sodlgiamtrongthang = 0;
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Thanh_Ly = 1 and  Datepart(Month,Ngay_ThanhLy) >= " + Thang + " and Datepart(Month,Ngay_ThanhLy) <= " + denthang + " and Datepart(Year,Ngay_ThanhLy) = " + Nam + "", conn);
                            sodlgiamtrongthang = (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);
                            biendongdltrongthang = ((int)clsCommon.Parse(sotangtrongthang, 0) + sodlgiamtrongthang).ToString();
                            cryRpt.DataDefinition.FormulaFields["tanggiam"].Text = "\"" + biendongdltrongthang + " đại lý" + "\"";
                            int sodldencuoithang = 0;
                            sodldencuoithang = (int)clsCommon.Parse(tongDLDauThang, 0) + (int)clsCommon.Parse(sotangtrongthang, 0) - sodlgiamtrongthang;
                            cryRpt.DataDefinition.FormulaFields["cuoi_ky"].Text = "\"" + sodldencuoithang.ToString() + " đại lý" + "\"";

                            cryRpt.DataDefinition.FormulaFields["thang1"].Text = "\"" + "Từ tháng " + Thang.ToString() + "  Đến tháng " + denthang.ToString() + "  Năm " + Nam.ToString() + "\"";
                            cryRpt.DataDefinition.FormulaFields["Don_Vi"].Text = "\"" + TenDV.ToUpper() + "\"";
                        }
                        else
                        {
                            da = new SqlDataAdapter("Select * from HopDong INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Datepart(Month,Ngay_HD) >=" + Thang + " and Datepart(Month,Ngay_HD) <=" + denthang + " and Datepart(year,Ngay_HD)=" + Nam + " and DMDonVI.Ma_DT=@madonvi", conn);
                            da.SelectCommand.Parameters.AddWithValue("@madonvi", MaDV);
                            dt = new DataTable();
                            da.Fill(dt);
                            foreach (DataRow rowDT in dt.Rows)
                            {
                                if (rowDT["Thanh_Ly"].ToString().Equals("1") || rowDT["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                    rowDT["Thanh_Ly"] = "FALSE";
                            }
                            da = new SqlDataAdapter("Select * from HOpdong INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Thanh_Ly =1 and Datepart(Month,Ngay_ThanhLy) >=" + Thang + " and Datepart(Month,Ngay_ThanhLy) <=" + denthang + " and Datepart(year,Ngay_ThanhLy)=" + Nam + " and DMDonVi.Ma_DT=@madonvi", conn);
                            da.SelectCommand.Parameters.AddWithValue("@madonvi", MaDV);
                            DataTable dtTL = new DataTable();
                            da.Fill(dtTL);
                            dt.Merge(dtTL);

                            foreach (DataRow rowNew in dt.Rows)
                            {
                                if (rowNew["Thanh_Ly"].ToString().Equals("1") || rowNew["Thanh_Ly"].ToString().ToUpper() == "TRUE")
                                {
                                    rowNew["Ly_Do"] = rowNew["Ma_DT"];
                                    rowNew["Ma_DT"] = "";
                                    rowNew["Ngay_HD"] = DBNull.Value;
                                }
                                else
                                {
                                    rowNew["Ly_Do"] = "";
                                    rowNew["Ngay_ThanhLy"] = DBNull.Value;
                                }
                            }
                            cryRpt.Database.Tables[0].SetDataSource(dt);
                            string tongDLDauThang = "";
                            DateTime dauthang = clsCommon.maxNgay(Nam, Thang - 1);
                            SqlCommand cmd = null;
                            cmd = new SqlCommand(@"Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1  and Datepart(day,Ngay_HD) <= Datepart(day,'" + dauthang.ToString("MM/dd/yyyy") + "') and Datepart(Month,Ngay_HD) <= Datepart(Month,'" + dauthang.ToString("MM/dd/yyyy") + "') and "
                        + " Datepart(year,Ngay_HD) <= Datepart(year,'" + dauthang.ToString("MM/dd/yyyy") + "')  and (Ngay_ThanhLy is null or Datepart(Month, Ngay_ThanhLy) > " + (Thang - 1).ToString() + ") and DMDonVi.Ma_DT=@madonvi", conn);
                            cmd.Parameters.AddWithValue("@madonvi", MaDV);
                            tongDLDauThang = cmd.ExecuteScalar().ToString();
                            cryRpt.DataDefinition.FormulaFields["dau_ky"].Text = "\"" + tongDLDauThang + " đại lý" + "\"";

                            string biendongdltrongthang = "";
                            string sotangtrongthang = "";
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1  and datepart(Month,Ngay_HD) >= " + Thang + " and datepart(Month,Ngay_HD) <= " + denthang + "  and datepart(year,Ngay_HD) = " + Nam + " and DMDonVi.Ma_DT=@madonvi", conn);
                            cmd.Parameters.AddWithValue("@madonvi", MaDV);
                            sotangtrongthang = cmd.ExecuteScalar().ToString();
                            int sodlgiamtrongthang = 0;
                            cmd = new SqlCommand("Select Count(HopDong.ID) from HOPDONG INNER JOIN DMCanBo ON HopDong.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDONVI ON DMDONVI.Ma_DT=DMCanBo.Ma_DMDonVi WHERE HopDong.isPublic=1  and DMCANBO.isPublic=1 and DMDONVI.isPublic=1 and Thanh_Ly = 1 and  Datepart(Month,Ngay_ThanhLy) >= " + Thang + " and Datepart(Month,Ngay_ThanhLy) <= " + denthang + " and Datepart(Year,Ngay_ThanhLy) = " + Nam + " and DMDonVi.Ma_DT=@madonvi", conn);
                            cmd.Parameters.AddWithValue("@madonvi", MaDV);
                            sodlgiamtrongthang = (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);
                            biendongdltrongthang = ((int)clsCommon.Parse(sotangtrongthang, 0) + sodlgiamtrongthang).ToString();
                            cryRpt.DataDefinition.FormulaFields["tanggiam"].Text = "\"" + biendongdltrongthang + " đại lý" + "\"";
                            int sodldencuoithang = 0;
                            sodldencuoithang = (int)clsCommon.Parse(tongDLDauThang, 0) + (int)clsCommon.Parse(sotangtrongthang, 0) - sodlgiamtrongthang;
                            cryRpt.DataDefinition.FormulaFields["cuoi_ky"].Text = "\"" + sodldencuoithang.ToString() + " đại lý" + "\"";

                            cryRpt.DataDefinition.FormulaFields["thang1"].Text = "\"" + "Từ tháng " + Thang.ToString() + "  Đến tháng " + denthang.ToString() + "  Năm " + Nam.ToString() + "\"";
                            cryRpt.DataDefinition.FormulaFields["Don_Vi"].Text = "\"" + TenDV.ToUpper() + "\"";
                        }
                    }

                    crystalReportViewer2.ReportSource = cryRpt;
                    crystalReportViewer2.Refresh();
                }
            }
            else if (formBaoCao == "CongTy")
            {
                fileName = Application.StartupPath + @"\BaoCao\DSDaily_Cty.rpt";
                if (!File.Exists(fileName))
                {
                    clsCommon.MsgBox("Không tìm thấy đường dẫn file " + fileName+" vui lòng kiểm tra lại !", 0);
                }
                else
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    cryRpt.Load(fileName);                    
                    int Nam = nam;
                    int Thang = thang;
                    int denthang = denThang;
                    DateTime dauthang = clsCommon.maxNgay(Nam, Thang - 1);
                    SqlDataAdapter da = null;
                    SqlCommand cmd = null;
                    da = new SqlDataAdapter("Select ROW_NUMBER() OVER(ORDER BY DMDONVI.ID) as STT, DMDONVI.Ma_DT as Ma_DonVi,DMDONVI.Ten_DT as Ten_DonVi from DMDONVI  Where DMDONVI.isPublic=1", conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    ds.rptDSDaiLy_Cty.Rows.Clear();
                    foreach (DataRow rowdonvi in dt.Rows)
                    {
                        DataRow rowDV = ds.rptDSDaiLy_Cty.NewRow();
                        rowDV["STT"] = rowdonvi["STT"];
                        rowDV["Ma_DonVi"] = rowdonvi["Ma_DonVi"];
                        rowDV["Ten_DonVi"] = rowdonvi["Ten_DonVi"];
                        ds.rptDSDaiLy_Cty.Rows.Add(rowDV);
                    }

                    string madonvi = "";
                    int tongDLDauThang = 0;
                    int sodldencuoithang = 0; int sodltangtrongthang = 0;
                    int sodlgiamtrongthang = 0;
                    foreach (DataRow rowDonVi in ds.rptDSDaiLy_Cty.Rows)
                    {
                        madonvi = rowDonVi["Ma_DonVi"].ToString();
                        da = new SqlDataAdapter("Select * from DMCANBO Where isPublic=1 and Ma_DMDonVi='" + madonvi + "'", conn);
                        DataTable dtCanBo = new DataTable();
                        da.Fill(dtCanBo);
                        tongDLDauThang = 0;
                        sodldencuoithang = 0; sodltangtrongthang = 0; sodlgiamtrongthang = 0;
                        if (Thang.CompareTo(denthang) == 0)
                        {
                            foreach (DataRow rowCanBo in dtCanBo.Rows)
                            {
                                cmd = new SqlCommand(@"Select Count(ID) from HOPDONG Where isPublic=1  and Datepart(day,Ngay_HD) <= Datepart(day,'" + dauthang.ToString("MM/dd/yyyy") + "') and Datepart(Month,Ngay_HD) <= Datepart(Month,'" + dauthang.ToString("MM/dd/yyyy") + "') and "
                             + " Datepart(year,Ngay_HD) <= Datepart(year,'" + dauthang.ToString("MM/dd/yyyy") + "') and Ma_DMCanBo='" + rowCanBo["Ma_DT"].ToString() + "'  and (Ngay_ThanhLy is null or Datepart(Month, Ngay_ThanhLy) > " + (Thang - 1).ToString() + ")", conn);
                                tongDLDauThang += (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);

                                cmd = new SqlCommand("Select Count(ID) from HOPDONG WHERE isPublic=1 and datepart(Month,Ngay_HD) = " + Thang + " and datepart(year,Ngay_HD) = " + Nam + " and Ma_DMCanBo='" + rowCanBo["Ma_DT"].ToString() + "'", conn);
                                sodltangtrongthang += (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);

                                cmd = new SqlCommand("Select Count(ID) from HOPDONG WHERE isPublic=1 and Thanh_Ly = 1 and datepart(Month,Ngay_ThanhLy) = " + Thang + " and  datepart(year,Ngay_ThanhLy) = " + Nam + " and Ma_DMCanBo='" + rowCanBo["Ma_DT"].ToString() + "'", conn);
                                sodlgiamtrongthang += (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);
                            }
                            rowDonVi["HD_DauThang"] = tongDLDauThang.ToString();
                            rowDonVi["HD_KyMoi"] = sodltangtrongthang.ToString();
                            rowDonVi["HD_ThanhLy"] = sodlgiamtrongthang.ToString();
                            rowDonVi["HD_CuoiThang"] = (tongDLDauThang + sodltangtrongthang - sodlgiamtrongthang).ToString();
                        }
                        else
                        {
                            foreach (DataRow rowCanBo in dtCanBo.Rows)
                            {
                                cmd = new SqlCommand(@"Select Count(ID) from HOPDONG Where isPublic=1  and Datepart(day,Ngay_HD) <= Datepart(day,'" + dauthang.ToString("MM/dd/yyyy") + "') and Datepart(Month,Ngay_HD) <= Datepart(Month,'" + dauthang.ToString("MM/dd/yyyy") + "') and "
                             + " Datepart(year,Ngay_HD) <= Datepart(year,'" + dauthang.ToString("MM/dd/yyyy") + "') and Ma_DMCanBo='" + rowCanBo["Ma_DT"].ToString() + "'  and (Ngay_ThanhLy is null or Datepart(Month, Ngay_ThanhLy) > " + (Thang - 1).ToString() + ")", conn);
                                tongDLDauThang += (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);

                                cmd = new SqlCommand("Select Count(ID) from HOPDONG WHERE isPublic=1 and datepart(Month,Ngay_HD) >= " + Thang + " and datepart(Month,Ngay_HD) <= " + denthang + " and datepart(year,Ngay_HD) = " + Nam + " and Ma_DMCanBo='" + rowCanBo["Ma_DT"].ToString() + "'", conn);
                                sodltangtrongthang += (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);

                                cmd = new SqlCommand("Select Count(ID) from HOPDONG WHERE isPublic=1 and Thanh_Ly = 1 and datepart(Month,Ngay_ThanhLy) >= " + Thang + " and datepart(Month,Ngay_ThanhLy) <= " + denthang + " and datepart(year,Ngay_ThanhLy) = " + Nam + " and Ma_DMCanBo='" + rowCanBo["Ma_DT"].ToString() + "'", conn);
                                sodlgiamtrongthang += (int)clsCommon.Parse(cmd.ExecuteScalar().ToString(), 0);
                            }
                            rowDonVi["HD_DauThang"] = tongDLDauThang.ToString();
                            rowDonVi["HD_KyMoi"] = sodltangtrongthang.ToString();
                            rowDonVi["HD_ThanhLy"] = sodlgiamtrongthang.ToString();
                            rowDonVi["HD_CuoiThang"] = (tongDLDauThang + sodltangtrongthang - sodlgiamtrongthang).ToString();
                        }
                    }

                    dt = ds.rptDSDaiLy_Cty;
                    cryRpt.Database.Tables[0].SetDataSource(dt);

                    if (Thang.CompareTo(denthang) == 0)
                    {
                        cryRpt.DataDefinition.FormulaFields["thang1"].Text = "\"" +"Tháng " + Thang.ToString() + "  Năm "+Nam + "\"";  
                    }    
                    else
                    {
                        cryRpt.DataDefinition.FormulaFields["thang1"].Text = "\"" +"Từ tháng " + Thang.ToString() + "  đến tháng "+denthang+ "  Năm "+Nam +"\"";  
                    }
                    int dthang = 0, kymoi = 0, thanhly = 0, cuoithang = 0;
                    foreach (DataRow rowDT in dt.Rows)
                    {
                        dthang += (int)clsCommon.Parse(rowDT["HD_DauThang"].ToString(), 0);
                        kymoi += (int)clsCommon.Parse(rowDT["HD_KyMoi"].ToString(), 0);
                        thanhly += (int)clsCommon.Parse(rowDT["HD_ThanhLy"].ToString(), 0);
                        cuoithang += (int)clsCommon.Parse(rowDT["HD_CuoiThang"].ToString(), 0);
                    }
                    cryRpt.DataDefinition.FormulaFields["DauThang"].Text = "\"" + dthang.ToString() + "\"";
                    cryRpt.DataDefinition.FormulaFields["KyMoi"].Text = "\"" + kymoi.ToString() + "\"";
                    cryRpt.DataDefinition.FormulaFields["ThanhLy"].Text = "\"" + thanhly.ToString() + "\"";
                    cryRpt.DataDefinition.FormulaFields["CuoiThang"].Text = "\"" + cuoithang.ToString() + "\"";

                    crystalReportViewer2.ReportSource = cryRpt;
                    crystalReportViewer2.Refresh();
                }
            }
            else
            {
                if (!File.Exists(@"BaoCao\HopDong2.rpt"))
                {
                    clsCommon.MsgBox("Không tìm thấy đường dẫn file " + Application.StartupPath + @"\BaoCao\HopDong2.rpt vui lòng kiểm tra lại !", 0);
                }
                else
                {
                    cryRpt.Load(@"BaoCao\HopDong2.rpt");
                    // cryRpt.Database.Tables[0].SetDataSource(dt);
                    cryRpt.DataDefinition.FormulaFields["So_HD"].Text = "\"" + @"So hop dong" + "\"";
                    //cryRpt.DataDefinition.FormulaFields["Ngay_HD"].Text = "\"" + @"Ngày HD" + "\"";
                    cryRpt.DataDefinition.FormulaFields["Ho_ten"].Text = "\"" + @"ABC" + "\"";
                    cryRpt.DataDefinition.FormulaFields["Dia_chi"].Text = "\"" + @"Vũ Ngọc Phan" + "\"";
                    cryRpt.DataDefinition.FormulaFields["CMND"].Text = "\"" + @"9876543" + "\"";
                    cryRpt.DataDefinition.FormulaFields["Cap_ngay"].Text = "\"" + @"Hôm nay" + "\"";
                    cryRpt.DataDefinition.FormulaFields["noi_cap"].Text = "\"" + @"Hà Nội" + "\"";
                    cryRpt.DataDefinition.FormulaFields["MST"].Text = "\"" + @"mã số thuế 0909" + "\"";
                    cryRpt.DataDefinition.FormulaFields["MA_DLY"].Text = "\"" + @"Mã đại lý" + "\"";
                    cryRpt.DataDefinition.FormulaFields["Noi_Ban"].Text = "\"" + @"NƠi bán" + "\"";
                    cryRpt.DataDefinition.FormulaFields["Ngay_HD"].Text = "\"" + @"01/01/2001" + "\"";
                    crystalReportViewer2.ReportSource = cryRpt;
                    crystalReportViewer2.Refresh();
                }
            }
        }

        #region cls event --------------------------------------------
        private void cls_OnAction(SqlException e)
        {
            cls_OnError(e);
        }

        private void cls_OnError(Exception e)
        {
            XtraMessageBox.Show("Một lỗi chưa được xử lý đã xảy ra. Chi tiết lỗi: \n" + e.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            this.Close();
        }
        #endregion ---------------------------------------------------

    }
}
